package co.gov.medellin.ssti.sub.paoyer.administration.dao;

import java.sql.Connection;
import java.sql.Statement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Collection;

import co.gov.medellin.ssti.sub.paoyer.administration.model.vo.AuthorizedByBranchVO;

/**
 * DAO para crear, consultar y actualizar registros en la vista
 * VFD_AUTORIZADOS_X_SURCURSAL
 * 
 * @author Rene.Rangel@consultsoft.com
 * @version 1.0
 */

public class AuthorizedByBranchDAO {
	/**
	 * Conexion a la base de datos.
	 */
	private Connection conn = null;

	/**
	 * Constructor que inicializa la conexion con la base de datos.
	 * 
	 * @param conn
	 *            Conexion con la base de datos.
	 */

	public AuthorizedByBranchDAO(Connection conn) {
		this.conn = conn;
	}

	/**
	 * Consulta un grupo de registros de la tabla TCS_OPCION a partir de las
	 * condiciones que vienen en el Value Object
	 * 
	 * @param OptionVO
	 *            Value Object con las condiciones a tener encuenta.
	 * 
	 * @return Collection Arreglo de registros retornados de la consulta.
	 */
	public Collection<AuthorizedByBranchVO> findAuthorizedBranchByCriterea(
			AuthorizedByBranchVO criterea) throws SQLException {

		/**
		 * Se consulta sobre la vista creada para este fin
		 */

		String query = "SELECT ausu.* "
				+ "FROM vfd_autorizados_x_sucursal ausu";

		String filters = "";
		// Filtro por Nombre de Sucursal
		if ((criterea.getBranchName() != null)
				&& !(criterea.getBranchName().equals(""))) {
			if (filters.equalsIgnoreCase("")) {
				filters = filters + " UPPER(ausu.sucu_nombre) LIKE '%"
						+ criterea.getBranchName().toUpperCase() + "%' ";
			} else {
				filters = filters + " AND UPPER(ausu.sucu_nombre) LIKE '%"
						+ criterea.getBranchName().toUpperCase() + "%' ";
			}
		}

		// Filtro por Codigo Usuario de Sucursal
		if ((criterea.getBranchCustomCode() != null)
				&& !(criterea.getBranchCustomCode().intValue() == 0)) {
			if (filters.equalsIgnoreCase("")) {
				filters = filters + " ausu.sucu_codusu = "
						+ criterea.getBranchCustomCode();
			} else {
				filters = filters + " AND ausu.sucu_codusu = "
						+ criterea.getBranchCustomCode();
			}
		}

		// Filtro por Codigo interno de Sucursal
		if ((criterea.getBranchInternalCode() != null)
				&& !(criterea.getBranchInternalCode().intValue() == 0)) {
			if (filters.equalsIgnoreCase("")) {
				filters = filters + " ausu.sucu_codin = "
						+ criterea.getBranchInternalCode();
			} else {
				filters = filters + " AND ausu.sucu_codin = "
						+ criterea.getBranchInternalCode();
			}
		}

		// Filtro por Nombre de Autorizado
		if ((criterea.getAuthorizedName() != null)
				&& !(criterea.getAuthorizedName().equals(""))) {
			if (filters.equalsIgnoreCase("")) {
				filters = filters + " ausu.autr_nombre = "
						+ criterea.getAuthorizedName().toUpperCase();
			} else {
				filters = filters + " AND ausu.autr_nombre = "
						+ criterea.getAuthorizedName().toUpperCase();
			}
		}

		// Filtro por Codigo Usuario de Autorizado
		if ((criterea.getAuthorizedCustomCode() != null)
				&& !(criterea.getAuthorizedCustomCode().intValue() == 0)) {
			if (filters.equalsIgnoreCase("")) {
				filters = filters + " ausu.autr_codusu = "
						+ criterea.getAuthorizedCustomCode();
			} else {
				filters = filters + " AND ausu.autr_codusu = "
						+ criterea.getAuthorizedCustomCode();
			}
		}

		// Filtro por Codigo Interno de Autorizado
		if ((criterea.getAuthorizedInternalCode() != null)
				&& !(criterea.getAuthorizedInternalCode().intValue() == 0)) {
			if (filters.equalsIgnoreCase("")) {
				filters = filters + " ausu.autr_codin = "
						+ criterea.getAuthorizedInternalCode();
			} else {
				filters = filters + " AND ausu.autr_codin = "
						+ criterea.getAuthorizedInternalCode();
			}
		}

		// Si hay filtros, entonces armar la clausula WHERE
		if (!filters.equals("")) {
			query = query + " WHERE " + filters;
		}

		Statement stm = null;
		ResultSet rst = null;
		ArrayList<AuthorizedByBranchVO> authorizedBranchs = new ArrayList<AuthorizedByBranchVO>();

		stm = conn.createStatement();
		rst = stm.executeQuery(query);

		while (rst.next()) {
			AuthorizedByBranchVO authorizedBranch = new AuthorizedByBranchVO();
			authorizedBranch.setBranchInternalCode(new Long(rst
					.getLong("sucu_codin")));
			authorizedBranch.setBranchCustomCode(new Short(rst
					.getShort("sucu_codusu")));
			authorizedBranch.setBranchName(rst.getString("sucu_nombre"));
			authorizedBranch.setBranchAddress(rst.getString("sucu_direccion"));
			authorizedBranch.setBranchPhoneNumber(rst
					.getString("sucu_telefono"));
			authorizedBranch.setActiveBranch(rst.getString("sucu_activo"));
			authorizedBranch.setBranchFax(rst.getString("sucu_fax"));
			authorizedBranch.setBranchPostOfficeBox(rst
					.getString("sucu_apartado_aereo"));
			authorizedBranch.setBranchCityCode(new Long(rst
					.getLong("sucu_ciu_codigo")));
			authorizedBranch.setBranchManager(rst.getString("sucu_gerente"));
			authorizedBranch.setAuthorizedInternalCode(new Long(rst
					.getLong("autr_codin")));
			authorizedBranch.setAuthorizedName(rst.getString("autr_nombre"));
			authorizedBranch.setAuthorizedRequests(rst
					.getString("autr_solicita"));
			authorizedBranch
					.setAuthorizedReceives(rst.getString("autr_recibe"));
			authorizedBranch.setActiveAuthorized(rst.getString("autr_activo"));
			authorizedBranch.setAuthorizedCustomCode(new Long(rst
					.getLong("autr_codusu")));
			authorizedBranchs.add(authorizedBranch);
		}

		stm.close();

		return authorizedBranchs;
	}

}